﻿CREATE PROCEDURE [maint].[DefragTable]
@DbName [sysname], @TableName [sysname], @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SELECT @RC = 0, @ERROR = 0;

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + ''''
			PRINT N'	@TableName = N''' + ISNULL(@TableName,N'NULL') + ''''
		END

	DECLARE @FragThreshold int
	SET @FragThreshold = maint.GetDefragThreshold(@DbName)

	DECLARE @ContigResultsCursor CURSOR
	EXEC @RC = maint.GetTableFragmentation @DbName, @TableName, @ContigResultsCursor OUTPUT
	IF(@RC <> 0) GOTO EXIT_PROC

	DECLARE @IndexName sysname, @LogicalFrag int, @Pages int
	FETCH NEXT FROM @ContigResultsCursor INTO @IndexName, @LogicalFrag, @Pages
	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			IF ((@LogicalFrag >= @FragThreshold) AND (@Pages > 25))--less than 25, index not used
				BEGIN
					BEGIN TRY
						EXEC @RC = maint.DefragIndex @DbName ,@TableName ,@IndexName ,@OutputDiagnostics;
					END TRY
					BEGIN CATCH
						SET @ERROR = @RC;					
					END CATCH
				END

			FETCH NEXT FROM @ContigResultsCursor INTO @IndexName, @LogicalFrag, @Pages
		END

	IF (CURSOR_STATUS('variable','@ContigResultsCursor') >= 0)
		BEGIN
			CLOSE @ContigResultsCursor
			DEALLOCATE @ContigResultsCursor
		END

	SET @RC = @ERROR;
	
EXIT_PROC:
	RETURN(@RC)
END